﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Backend.DTO;
using System.Data;

namespace Backend.DAL
{
    class StudyDAL
    {
        private SqlConnection sqlConnection;

        public StudyDAL()
        {
            DbConn aConn = new DbConn();
            sqlConnection = aConn.GetUssdSqlConnection;
        }


        #region Study

        public StudyDTO GetStudy(int studyId)
        {
            StudyDTO aStudyDto = null;

            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("SELECT TOP(1) * FROM Studies WHERE Id = @StudyId", sqlConnection);
                command.Parameters.AddWithValue("StudyId", studyId);

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        aStudyDto = new StudyDTO();

                        aStudyDto.Id = Convert.ToInt32(reader["Id"]);
                        aStudyDto.Name = reader["Name"].ToString();
                        aStudyDto.CreatedBy = Convert.ToInt32(reader["CreatedBy"]);
                        aStudyDto.SharedBy = Convert.ToInt32(reader["SharedBy"]);
                        aStudyDto.SharedMessage = reader["SharedMessage"].ToString();
                        aStudyDto.IsSaved = Convert.ToBoolean(reader["IsSaved"].ToString());

                        if (reader["CreationDate"] != null)
                        {
                            aStudyDto.CreationDate = Convert.ToDateTime(reader["CreationDate"]);
                        }
                        if (reader["ModifiedDate"] != null)
                        {
                            aStudyDto.ModifiedDate = Convert.ToDateTime(reader["ModifiedDate"]);
                        }
                        
                        break;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }

            return aStudyDto;
        }

        public List<StudyDTO> GetSavedStudiesByUserId(int userId)
        {
            List<StudyDTO> listOfStudies = null;

            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("SELECT * FROM Studies WHERE CreatedBy = @UserId AND IsSaved > 0 ORDER BY ModifiedDate DESC", sqlConnection);
                command.Parameters.AddWithValue("UserId", userId);

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    listOfStudies = new List<StudyDTO>();

                    while (reader.Read())
                    {
                        StudyDTO aStudyDto = new StudyDTO();

                        aStudyDto.Id = Convert.ToInt32(reader["Id"]);
                        aStudyDto.Name = reader["Name"].ToString();
                        aStudyDto.CreatedBy = Convert.ToInt32(reader["CreatedBy"]);
                        aStudyDto.SharedBy = Convert.ToInt32(reader["SharedBy"]);
                        aStudyDto.SharedMessage = reader["SharedMessage"].ToString();
                        aStudyDto.IsSaved = Convert.ToBoolean(reader["IsSaved"].ToString());

                        if (reader["CreationDate"] != null)
                        {
                            aStudyDto.CreationDate = Convert.ToDateTime(reader["CreationDate"]);
                        }
                        if (reader["ModifiedDate"] != null)
                        {
                            aStudyDto.ModifiedDate = Convert.ToDateTime(reader["ModifiedDate"]);
                        }

                        listOfStudies.Add(aStudyDto);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }

            return listOfStudies;
        }

        public StudyDTO GetUnsavedStudyByUserId(int userId)
        {
            StudyDTO aStudyDto = null;

            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("SELECT TOP(1) * FROM Studies WHERE CreatedBy = @UserId AND IsSaved = 0", sqlConnection);
                command.Parameters.AddWithValue("UserId", userId);

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        aStudyDto = new StudyDTO();

                        aStudyDto.Id = Convert.ToInt32(reader["Id"]);
                        aStudyDto.Name = reader["Name"].ToString();
                        aStudyDto.CreatedBy = Convert.ToInt32(reader["CreatedBy"]);
                        aStudyDto.SharedBy = Convert.ToInt32(reader["SharedBy"]);
                        aStudyDto.SharedMessage = reader["SharedMessage"].ToString();
                        aStudyDto.IsSaved = Convert.ToBoolean(reader["IsSaved"].ToString());

                        if (reader["CreationDate"] != null)
                        {
                            aStudyDto.CreationDate = Convert.ToDateTime(reader["CreationDate"]);
                        }
                        if (reader["ModifiedDate"] != null)
                        {
                            aStudyDto.ModifiedDate = Convert.ToDateTime(reader["ModifiedDate"]);
                        }

                        break;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }

            return aStudyDto;
        }

        public int AddStudy(StudyDTO aStudyDTO)
        {
            try
            {
                sqlConnection.Open();

                string sql = "INSERT INTO Studies(Name, CreatedBy, SharedBy, SharedMessage, IsSaved, CreationDate, ModifiedDate) " +
                             "VALUES(@name, @createdBy, @sharedBy, @sharedMessage, @isSaved, @creationDate, @modifiedDate)";
                SqlCommand command = new SqlCommand(sql, sqlConnection);

                command.Parameters.AddWithValue("name", aStudyDTO.Name);
                command.Parameters.AddWithValue("createdBy", aStudyDTO.CreatedBy);
                command.Parameters.AddWithValue("sharedBy", aStudyDTO.SharedBy);
                command.Parameters.AddWithValue("sharedMessage", aStudyDTO.SharedMessage);
                command.Parameters.AddWithValue("isSaved", aStudyDTO.IsSaved);
                command.Parameters.AddWithValue("creationDate", DateTime.Now);
                command.Parameters.AddWithValue("modifiedDate", DateTime.Now);

                command.ExecuteNonQuery();

                // get last inserted Id
                int lastInsertId = 0;
                string sqlLastId = "SELECT IDENT_CURRENT('Studies')";
                SqlCommand commandLastId = new SqlCommand(sqlLastId, sqlConnection);

                lastInsertId = Convert.ToInt32(commandLastId.ExecuteScalar());
                return lastInsertId;
            }
            catch (SqlException sqlException)
            {
                throw sqlException;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }
        }

        public void UpdateStudy(StudyDTO aStudyDto)
        {
            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("UPDATE Studies SET " +
                                                        "Name = @name, " +
                                                        "CreatedBy = @createdBy, " +
                                                        "SharedBy = @sharedBy, " +
                                                        "SharedMessage = @sharedMessage, " +
                                                        "IsSaved = @isSaved, " +
                                                        "ModifiedDate = @modifiedDate " +
                                                    "WHERE Id = @studyId", sqlConnection);
                command.Parameters.AddWithValue("name", aStudyDto.Name);
                command.Parameters.AddWithValue("createdBy", aStudyDto.CreatedBy);
                command.Parameters.AddWithValue("sharedBy", aStudyDto.SharedBy);
                command.Parameters.AddWithValue("sharedMessage", aStudyDto.SharedMessage);
                command.Parameters.AddWithValue("isSaved", aStudyDto.IsSaved);
                command.Parameters.AddWithValue("modifiedDate", DateTime.Now);
                command.Parameters.AddWithValue("studyId", aStudyDto.Id);
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }
        }

        public bool DeleteStudy(int studyId)
        {
            try
            {
                // Cascade delete
                List<int> ListOfInclusionsAndParametersId = new List<int>();
                List<InclusionOrParameterDTO> listOfInclusions = GetInclusionsByStudyId(studyId);
                List<InclusionOrParameterDTO> listOfParameters = GetParametersByStudyId(studyId);

                for (int i = 0; i < listOfInclusions.Count; i++)
                {
                    ListOfInclusionsAndParametersId.Add(listOfInclusions[i].Id);
                }

                for (int i = 0; i < listOfParameters.Count; i++)
                {
                    ListOfInclusionsAndParametersId.Add(listOfParameters[i].Id);
                }

                for (int i = 0; i < ListOfInclusionsAndParametersId.Count; i++)
                {
                    DeleteInclusionOrParameter(ListOfInclusionsAndParametersId[i]);
                }

                // cascade delete complete. delete the study
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("DELETE FROM Studies WHERE Id = @id", sqlConnection);
                command.Parameters.AddWithValue("id", studyId);

                if (command.ExecuteNonQuery() > 0)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }
        }

        public int ShareStudy(int studyId, int userId, int shareToUserId, string shareMessage)
        {
            try
            {
                sqlConnection.Open();
                SqlCommand command = new SqlCommand("CopyStudy", sqlConnection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@Id", studyId));
                command.Parameters.Add(new SqlParameter("@UserId", userId));
                command.Parameters.Add(new SqlParameter("@ShareToUserId", shareToUserId));
                command.Parameters.Add(new SqlParameter("@ShareMessage", shareMessage));

                return command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }
        }

        #endregion


        #region Inclusions And Parameter

        public List<InclusionOrParameterDTO> GetInclusionsByStudyId(int studyId)
        {
            List<InclusionOrParameterDTO> listOfInclusions = new List<InclusionOrParameterDTO>();

            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("SELECT * FROM InclusionsAndParameters WHERE StudyId = @StudyId AND Type = 'Inclusion'", sqlConnection);
                command.Parameters.AddWithValue("StudyId", studyId);

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        InclusionOrParameterDTO aInclusionDTO = new InclusionOrParameterDTO();
                        aInclusionDTO.Id = Convert.ToInt32(reader["Id"]);
                        aInclusionDTO.StudyId = Convert.ToInt32(reader["StudyId"]);
                        aInclusionDTO.Name = reader["Name"].ToString();
                        aInclusionDTO.Type = reader["Type"].ToString();
                        aInclusionDTO.CreatedBy = Convert.ToInt32(reader["CreatedBy"]);
                        aInclusionDTO.SharedBy = Convert.ToInt32(reader["SharedBy"]);
                        aInclusionDTO.SharedMessage = reader["SharedMessage"].ToString();
                        aInclusionDTO.CreationDate = Convert.ToDateTime(reader["CreationDate"]);

                        listOfInclusions.Add(aInclusionDTO);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }

            return listOfInclusions;
        }

        public List<InclusionOrParameterDTO> GetSharedInclusionsByUserId(int userId)
        {
            List<InclusionOrParameterDTO> listOfInclusions = new List<InclusionOrParameterDTO>();

            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("SELECT * FROM InclusionsAndParameters WHERE StudyId = -1 AND CreatedBy = @UserId AND Type = 'Inclusion'", sqlConnection);
                command.Parameters.AddWithValue("UserId", userId);

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        InclusionOrParameterDTO aInclusionDTO = new InclusionOrParameterDTO();
                        aInclusionDTO.Id = Convert.ToInt32(reader["Id"]);
                        aInclusionDTO.StudyId = Convert.ToInt32(reader["StudyId"]);
                        aInclusionDTO.Name = reader["Name"].ToString();
                        aInclusionDTO.Type = reader["Type"].ToString();
                        aInclusionDTO.CreatedBy = Convert.ToInt32(reader["CreatedBy"]);
                        aInclusionDTO.SharedBy = Convert.ToInt32(reader["SharedBy"]);
                        aInclusionDTO.SharedMessage = reader["SharedMessage"].ToString();
                        aInclusionDTO.CreationDate = Convert.ToDateTime(reader["CreationDate"]);

                        listOfInclusions.Add(aInclusionDTO);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }

            return listOfInclusions;
        }

        public List<InclusionOrParameterDTO> GetParametersByStudyId(int studyId)
        {
            List<InclusionOrParameterDTO> listOfParameters = new List<InclusionOrParameterDTO>();

            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("SELECT * FROM InclusionsAndParameters WHERE StudyId = @StudyId AND Type = 'Parameter'", sqlConnection);
                command.Parameters.AddWithValue("StudyId", studyId);

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        InclusionOrParameterDTO aParameterDTO = new InclusionOrParameterDTO();
                        aParameterDTO.Id = Convert.ToInt32(reader["Id"]);
                        aParameterDTO.StudyId = Convert.ToInt32(reader["StudyId"]);
                        aParameterDTO.Name = reader["Name"].ToString();
                        aParameterDTO.Type = reader["Type"].ToString();
                        aParameterDTO.CreatedBy = Convert.ToInt32(reader["CreatedBy"]);
                        aParameterDTO.SharedBy = Convert.ToInt32(reader["SharedBy"]);
                        aParameterDTO.SharedMessage = reader["SharedMessage"].ToString();
                        aParameterDTO.CreationDate = Convert.ToDateTime(reader["CreationDate"]);

                        listOfParameters.Add(aParameterDTO);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }

            return listOfParameters;
        }

        public List<InclusionOrParameterDTO> GetSharedParametersByUserId(int userId)
        {
            List<InclusionOrParameterDTO> listOfParameters = new List<InclusionOrParameterDTO>();

            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("SELECT * FROM InclusionsAndParameters WHERE StudyId = -1 AND CreatedBy = @UserId AND Type = 'Parameter'", sqlConnection);
                command.Parameters.AddWithValue("UserId", userId);

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        InclusionOrParameterDTO aParameterDTO = new InclusionOrParameterDTO();
                        aParameterDTO.Id = Convert.ToInt32(reader["Id"]);
                        aParameterDTO.StudyId = Convert.ToInt32(reader["StudyId"]);
                        aParameterDTO.Name = reader["Name"].ToString();
                        aParameterDTO.Type = reader["Type"].ToString();
                        aParameterDTO.CreatedBy = Convert.ToInt32(reader["CreatedBy"]);
                        aParameterDTO.SharedBy = Convert.ToInt32(reader["SharedBy"]);
                        aParameterDTO.SharedMessage = reader["SharedMessage"].ToString();
                        aParameterDTO.CreationDate = Convert.ToDateTime(reader["CreationDate"]);

                        listOfParameters.Add(aParameterDTO);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }

            return listOfParameters;
        }

        public InclusionOrParameterDTO GetInclusionOrParameter(int inclusionOrParameterId)
        {
            InclusionOrParameterDTO aInclusionOrParameterDTO = null;

            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("SELECT * FROM InclusionsAndParameters WHERE Id = @InclusionOrParameterId", sqlConnection);
                command.Parameters.AddWithValue("InclusionOrParameterId", inclusionOrParameterId);

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        aInclusionOrParameterDTO = new InclusionOrParameterDTO();
                        aInclusionOrParameterDTO.Id = Convert.ToInt32(reader["Id"]);
                        aInclusionOrParameterDTO.StudyId = Convert.ToInt32(reader["StudyId"]);
                        aInclusionOrParameterDTO.Name = reader["Name"].ToString();
                        aInclusionOrParameterDTO.Type = reader["Type"].ToString();
                        aInclusionOrParameterDTO.CreatedBy = Convert.ToInt32(reader["CreatedBy"]);
                        aInclusionOrParameterDTO.SharedBy = Convert.ToInt32(reader["SharedBy"]);
                        aInclusionOrParameterDTO.SharedMessage = reader["SharedMessage"].ToString();
                        aInclusionOrParameterDTO.CreationDate = Convert.ToDateTime(reader["CreationDate"]);

                        break;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }

            return aInclusionOrParameterDTO;
        }

        public void AddInclusionOrParameter(InclusionOrParameterDTO aInclusionOrParameterDTO)
        {
            try
            {
                sqlConnection.Open();

                string sql = "INSERT INTO InclusionsAndParameters(StudyId, Name, Type, CreatedBy, SharedBy, SharedMessage, CreationDate) " +
                             "VALUES(@studyId, @name, @type, @createdBy, @sharedBy, @sharedMessage, @creationDate)";
                SqlCommand command = new SqlCommand(sql, sqlConnection);

                command.Parameters.AddWithValue("studyId", aInclusionOrParameterDTO.StudyId);
                command.Parameters.AddWithValue("name", aInclusionOrParameterDTO.Name);
                command.Parameters.AddWithValue("type", aInclusionOrParameterDTO.Type);
                command.Parameters.AddWithValue("createdBy", aInclusionOrParameterDTO.CreatedBy);
                command.Parameters.AddWithValue("sharedBy", 0);
                command.Parameters.AddWithValue("sharedMessage", DBNull.Value);
                command.Parameters.AddWithValue("creationDate", DateTime.Now);

                command.ExecuteNonQuery();
            }
            catch (SqlException sqlException)
            {
                throw sqlException;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }
        }

        public void UpdateInclusionOrParameter(InclusionOrParameterDTO aInclusionOrParameterDTO)
        {
            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("UPDATE InclusionsAndParameters SET " +
                                                        "StudyId = @studyId, " +
                                                        "Name = @name, " +
                                                        "Type = @type, " +
                                                        "CreatedBy = @createdBy, " +
                                                        "SharedBy = @sharedBy " +
                                                    "WHERE Id = @id", sqlConnection);
                command.Parameters.AddWithValue("studyId", aInclusionOrParameterDTO.StudyId);
                command.Parameters.AddWithValue("name", aInclusionOrParameterDTO.Name);
                command.Parameters.AddWithValue("type", aInclusionOrParameterDTO.Type);
                command.Parameters.AddWithValue("createdBy", aInclusionOrParameterDTO.CreatedBy);
                command.Parameters.AddWithValue("sharedBy", aInclusionOrParameterDTO.SharedBy);
                command.Parameters.AddWithValue("id", aInclusionOrParameterDTO.Id);
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }
        }

        public void DeleteInclusionOrParameter(int inclusionOrParameterId)
        {
            try
            {
                // Cascade delete
                DeleteOutcomeByInclusionOrParameterId(inclusionOrParameterId);

                sqlConnection.Open();

                SqlCommand command = new SqlCommand("DELETE FROM InclusionsAndParameters WHERE Id = @id", sqlConnection);
                command.Parameters.AddWithValue("id", inclusionOrParameterId);
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }
        }

        public int ShareInclusionOrParameter(int inclusionOrParameterId, int userId, int shareToUserId)
        {
            try
            {
                sqlConnection.Open();
                SqlCommand command = new SqlCommand("CopyInclusionOrParameter", sqlConnection);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@Id", inclusionOrParameterId));
                command.Parameters.Add(new SqlParameter("@StudyId", -1));
                command.Parameters.Add(new SqlParameter("@UserId", userId));
                command.Parameters.Add(new SqlParameter("@ShareToUserId", shareToUserId));

                return command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }
        }

        #endregion


        #region Outcomes

        public List<OutcomeDTO> GetOutcomesByInclusionOrParameterId(int inclusionOrParameterId)
        {
            List<OutcomeDTO> listOfOutcomes = new List<OutcomeDTO>();

            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("SELECT * FROM Outcomes WHERE InclusionOrParameterId = @InclusionOrParameterId", sqlConnection);
                command.Parameters.AddWithValue("InclusionOrParameterId", inclusionOrParameterId);

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        OutcomeDTO aOutcomeDTO = new OutcomeDTO();
                        aOutcomeDTO.Id = (int)reader["Id"];
                        aOutcomeDTO.InclusionOrParameterId = Convert.ToInt32(reader["InclusionOrParameterId"]);
                        aOutcomeDTO.FieldName = reader["FieldName"].ToString();
                        aOutcomeDTO.FieldValue = reader["FieldValue"].ToString();

                        listOfOutcomes.Add(aOutcomeDTO);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }

            return listOfOutcomes;
        }

        public OutcomeDTO GetOutcome(int outcomeId)
        {
            OutcomeDTO aOutcomeDTO = null;

            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("SELECT * FROM Outcomes WHERE Id = @OutcomeId", sqlConnection);
                command.Parameters.AddWithValue("OutcomeId", outcomeId);

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        aOutcomeDTO = new OutcomeDTO();
                        aOutcomeDTO.Id = (int)reader["Id"];
                        aOutcomeDTO.InclusionOrParameterId = Convert.ToInt32(reader["InclusionOrParameterId"]);
                        aOutcomeDTO.FieldName = reader["FieldName"].ToString();
                        aOutcomeDTO.FieldValue = reader["FieldValue"].ToString();

                        break;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }

            return aOutcomeDTO;
        }

        public void AddOutcome(OutcomeDTO aOutcomeDTO)
        {
            try
            {
                sqlConnection.Open();

                string sql = "INSERT INTO Outcomes(InclusionOrParameterId, FieldName, FieldValue) " +
                             "VALUES(@inclusionId, @fieldName, @fieldValue)";
                SqlCommand command = new SqlCommand(sql, sqlConnection);

                command.Parameters.AddWithValue("inclusionId", aOutcomeDTO.InclusionOrParameterId);
                command.Parameters.AddWithValue("fieldName", aOutcomeDTO.FieldName);
                command.Parameters.AddWithValue("fieldValue", aOutcomeDTO.FieldValue);

                command.ExecuteNonQuery();

                // get last inserted Id
                int lastInsertId = 0;
                string sqlLastId = "SELECT IDENT_CURRENT('Outcomes')";
                SqlCommand commandLastId = new SqlCommand(sqlLastId, sqlConnection);

                lastInsertId = Convert.ToInt32(commandLastId.ExecuteScalar());

                // insert OutcomeDetails
                string sqlOutcomeDetails = "INSERT INTO OutcomeDetails(OutcomeId) " +
                                           "VALUES(@OutcomeId)";
                SqlCommand commandOutcomeDetails = new SqlCommand(sqlOutcomeDetails, sqlConnection);

                commandOutcomeDetails.Parameters.AddWithValue("OutcomeId", lastInsertId);
                commandOutcomeDetails.ExecuteNonQuery();
            }
            catch (SqlException sqlException)
            {
                throw sqlException;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }
        }

        public void DeleteOutcome(int outcomeId)
        {
            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("DELETE FROM Outcomes WHERE Id = @id", sqlConnection);
                command.Parameters.AddWithValue("id", outcomeId);
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }
        }

        public void DeleteOutcomeByInclusionOrParameterId(int inclusionOrParameterId)
        {
            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("DELETE FROM Outcomes WHERE InclusionOrParameterId = @id", sqlConnection);
                command.Parameters.AddWithValue("id", inclusionOrParameterId);
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }
        }

        #endregion


        #region Outcome Details

        public OutcomeDetailsDTO GetOutcomeDetails(int id)
        {
            OutcomeDetailsDTO aOutcomeDetailsDTO = null;

            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("SELECT * FROM OutcomeDetails WHERE OutcomeId = @Id", sqlConnection);
                command.Parameters.AddWithValue("Id", id);

                SqlDataReader reader = command.ExecuteReader();

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        aOutcomeDetailsDTO = new OutcomeDetailsDTO();
                        aOutcomeDetailsDTO.OutcomeId = Convert.ToInt32(reader["OutcomeId"]);
                        aOutcomeDetailsDTO.CodeType = Convert.ToInt32(reader["CodeType"]);
                        aOutcomeDetailsDTO.CodeId = Convert.ToInt32(reader["CodeId"]);
                        aOutcomeDetailsDTO.CodeRestrictType = Convert.ToInt32(reader["CodeRestrictType"]);
                        aOutcomeDetailsDTO.CodeRestrictField1 = reader["CodeRestrictField1"].ToString();
                        aOutcomeDetailsDTO.CodeRestrictField2 = reader["CodeRestrictField2"].ToString();
                        aOutcomeDetailsDTO.DateType = Convert.ToInt32(reader["DateType"]);
                        aOutcomeDetailsDTO.DateField1 = reader["DateField1"].ToString();
                        aOutcomeDetailsDTO.DateField2 = reader["DateField2"].ToString();
                        aOutcomeDetailsDTO.ReadingsType = Convert.ToInt32(reader["ReadingsType"]);
                        aOutcomeDetailsDTO.ReadingsId = Convert.ToInt32(reader["ReadingsId"]);
                        aOutcomeDetailsDTO.ReadingsRestrictType = Convert.ToInt32(reader["ReadingsRestrictType"]);
                        aOutcomeDetailsDTO.ReadingsRestrictField1 = reader["ReadingsRestrictField1"].ToString();
                        aOutcomeDetailsDTO.ReadingsRestrictField2 = reader["ReadingsRestrictField2"].ToString();
                        aOutcomeDetailsDTO.TextType = Convert.ToInt32(reader["TextType"]);
                        aOutcomeDetailsDTO.TextField1 = reader["TextField1"].ToString();
                        aOutcomeDetailsDTO.TextField2 = reader["TextField2"].ToString();
                        aOutcomeDetailsDTO.TextField3 = reader["TextField3"].ToString();
                        aOutcomeDetailsDTO.TextRestrictType = Convert.ToInt32(reader["TextRestrictType"]);
                        aOutcomeDetailsDTO.TextRestrictField1 = reader["TextRestrictField1"].ToString();
                        aOutcomeDetailsDTO.TextRestrictField2 = reader["TextRestrictField2"].ToString();
                        aOutcomeDetailsDTO.RadiologyType = Convert.ToInt32(reader["RadiologyType"]);
                        aOutcomeDetailsDTO.RadiologyId = Convert.ToInt32(reader["RadiologyId"]);
                        aOutcomeDetailsDTO.RadiologyRestrictType = Convert.ToInt32(reader["RadiologyRestrictType"]);
                        aOutcomeDetailsDTO.RadiologyRestrictField1 = reader["RadiologyRestrictField1"].ToString();
                        aOutcomeDetailsDTO.RadiologyRestrictField2 = reader["RadiologyRestrictField2"].ToString();
                        aOutcomeDetailsDTO.SnomedCtType = Convert.ToInt32(reader["SnomedCtType"]);
                        aOutcomeDetailsDTO.SnomedCtId = Convert.ToInt32(reader["SnomedCtId"]);
                        aOutcomeDetailsDTO.SnomedCtRestrictType = Convert.ToInt32(reader["SnomedCtRestrictType"]);
                        aOutcomeDetailsDTO.SnomedCtRestrictField1 = reader["SnomedCtRestrictField1"].ToString();
                        aOutcomeDetailsDTO.SnomedCtRestrictField2 = reader["SnomedCtRestrictField2"].ToString();
                        
                        break;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }

            return aOutcomeDetailsDTO;
        }

        public bool UpdateOutcomeDetails(OutcomeDetailsDTO aOutcomeDetailsDTO)
        {
            string param = string.Empty;

            if (aOutcomeDetailsDTO.CodeType > 0)
            {
                param += ", CodeType = " + aOutcomeDetailsDTO.CodeType;
            }

            if (aOutcomeDetailsDTO.CodeId > 0)
            {
                param += ", CodeId = " + aOutcomeDetailsDTO.CodeId;
            }

            if (aOutcomeDetailsDTO.CodeRestrictType > 0)
            {
                param += ", CodeRestrictType = " + aOutcomeDetailsDTO.CodeRestrictType;
            }

            if (!string.IsNullOrEmpty(aOutcomeDetailsDTO.CodeRestrictField1))
            {
                param += ", CodeRestrictField1 = '" + aOutcomeDetailsDTO.CodeRestrictField1 + "' ";
            }

            if (!string.IsNullOrEmpty(aOutcomeDetailsDTO.CodeRestrictField2))
            {
                param += ", CodeRestrictField2 = '" + aOutcomeDetailsDTO.CodeRestrictField2 + "' ";
            }

            if (aOutcomeDetailsDTO.DateType > 0)
            {
                param += ", DateType = " + aOutcomeDetailsDTO.DateType;
            }

            if (!string.IsNullOrEmpty(aOutcomeDetailsDTO.DateField1))
            {
                param += ", DateField1 = '" + aOutcomeDetailsDTO.DateField1 + "' ";
            }

            if (!string.IsNullOrEmpty(aOutcomeDetailsDTO.DateField2))
            {
                param += ", DateField2 = '" + aOutcomeDetailsDTO.DateField2 + "' ";
            }

            if (aOutcomeDetailsDTO.ReadingsType > 0)
            {
                param += ", ReadingsType = " + aOutcomeDetailsDTO.ReadingsType;
            }

            if (aOutcomeDetailsDTO.ReadingsId > 0)
            {
                param += ", ReadingsId = " + aOutcomeDetailsDTO.ReadingsId;
            }
            
            if (aOutcomeDetailsDTO.ReadingsRestrictType > 0)
            {
                param += ", ReadingsRestrictType = " + aOutcomeDetailsDTO.ReadingsRestrictType;
            }

            if (!string.IsNullOrEmpty(aOutcomeDetailsDTO.ReadingsRestrictField1))
            {
                param += ", ReadingsRestrictField1 = '" + aOutcomeDetailsDTO.ReadingsRestrictField1 + "' ";
            }

            if (!string.IsNullOrEmpty(aOutcomeDetailsDTO.ReadingsRestrictField2))
            {
                param += ", ReadingsRestrictField2 = '" + aOutcomeDetailsDTO.ReadingsRestrictField2 + "' ";
            }

            if (aOutcomeDetailsDTO.TextType > 0)
            {
                param += ", TextType = " + aOutcomeDetailsDTO.TextType;
            }

            if (!string.IsNullOrEmpty(aOutcomeDetailsDTO.TextField1))
            {
                param += ", TextField1 = '" + aOutcomeDetailsDTO.TextField1 + "' ";
            }

            if (!string.IsNullOrEmpty(aOutcomeDetailsDTO.TextField2))
            {
                param += ", TextField2 = '" + aOutcomeDetailsDTO.TextField2 + "' ";
            }

            if (!string.IsNullOrEmpty(aOutcomeDetailsDTO.TextField3))
            {
                param += ", TextField3 = '" + aOutcomeDetailsDTO.TextField3 + "' ";
            }

            if (aOutcomeDetailsDTO.TextRestrictType > 0)
            {
                param += ", TextRestrictType = " + aOutcomeDetailsDTO.TextRestrictType;
            }

            if (!string.IsNullOrEmpty(aOutcomeDetailsDTO.TextRestrictField1))
            {
                param += ", TextRestrictField1 = '" + aOutcomeDetailsDTO.TextRestrictField1 + "' ";
            }

            if (!string.IsNullOrEmpty(aOutcomeDetailsDTO.TextRestrictField2))
            {
                param += ", TextRestrictField2 = '" + aOutcomeDetailsDTO.TextRestrictField2 + "' ";
            }

            if (aOutcomeDetailsDTO.RadiologyType > 0)
            {
                param += ", RadiologyType = " + aOutcomeDetailsDTO.RadiologyType;
            }

            if (aOutcomeDetailsDTO.RadiologyId > 0)
            {
                param += ", RadiologyId = " + aOutcomeDetailsDTO.RadiologyId;
            }

            if (aOutcomeDetailsDTO.RadiologyRestrictType > 0)
            {
                param += ", RadiologyRestrictType = " + aOutcomeDetailsDTO.RadiologyRestrictType;
            }

            if (!string.IsNullOrEmpty(aOutcomeDetailsDTO.RadiologyRestrictField1))
            {
                param += ", RadiologyRestrictField1 = '" + aOutcomeDetailsDTO.RadiologyRestrictField1 + "' ";
            }

            if (!string.IsNullOrEmpty(aOutcomeDetailsDTO.RadiologyRestrictField2))
            {
                param += ", RadiologyRestrictField2 = '" + aOutcomeDetailsDTO.RadiologyRestrictField2 + "' ";
            }

            if (aOutcomeDetailsDTO.SnomedCtType > 0)
            {
                param += ", SnomedCtType = " + aOutcomeDetailsDTO.SnomedCtType;
            }

            if (aOutcomeDetailsDTO.SnomedCtId > 0)
            {
                param += ", SnomedCtId = " + aOutcomeDetailsDTO.SnomedCtId;
            }

            if (aOutcomeDetailsDTO.SnomedCtRestrictType > 0)
            {
                param += ", SnomedCtRestrictType = " + aOutcomeDetailsDTO.SnomedCtRestrictType;
            }

            if (!string.IsNullOrEmpty(aOutcomeDetailsDTO.SnomedCtRestrictField1))
            {
                param += ", SnomedCtRestrictField1 = '" + aOutcomeDetailsDTO.SnomedCtRestrictField1 + "' ";
            }

            if (!string.IsNullOrEmpty(aOutcomeDetailsDTO.SnomedCtRestrictField2))
            {
                param += ", SnomedCtRestrictField2 = '" + aOutcomeDetailsDTO.SnomedCtRestrictField2 + "' ";
            }
            
            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("UPDATE OutcomeDetails SET " + 
                                                        "OutcomeId=@Defaultfield" + param +
                                                    "WHERE OutcomeId=@outcomeId", sqlConnection);
                command.Parameters.AddWithValue("Defaultfield", aOutcomeDetailsDTO.OutcomeId);
                command.Parameters.AddWithValue("OutcomeId", aOutcomeDetailsDTO.OutcomeId);
                command.ExecuteNonQuery();

                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }
        }

        public void DeleteOutcomeDetails(int outcomeDetailsId)
        {
            try
            {
                sqlConnection.Open();

                SqlCommand command = new SqlCommand("DELETE FROM OutcomeDetails WHERE OutcomeId = @id", sqlConnection);
                command.Parameters.AddWithValue("id", outcomeDetailsId);
                command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConnection.State != ConnectionState.Closed)
                {
                    sqlConnection.Close();
                }
            }
        }

        #endregion
    }
}
